package com.kaishengit.dao;



import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.kaisengit.enity.Company;
import com.kaisengit.enity.Guide;
import com.kaisengit.enity.Sales;






import com.kaishengit.util.DBHelp;
import com.kaishengit.util.Pager;
import com.kaishengit.util.RowMapper;
import com.kaishengit.util.StringUtil;

public class ListDao {

	
		
	private DBHelp <Sales> db = new DBHelp<Sales>();

	public void save(Sales sales) {
		String sql = "insert into t_list (id,compayid,guideid,time,peoplenum,money,price,address) values (?,?,?,?,?,?,?,?)";
		db.executeSQL(sql, sales.getId(),sales.getCompany().getId(),sales.getGuide().getId() ,sales.getTime(),sales.getPeoplenum(),sales.getMoney(),sales.getPrice(),sales.getAddress());
		
	}

	public List<Sales> findAll() {
		String sql = "SELECT t_list.id,compayid,t_company.name AS 'name',t_guide.guidename AS 'guidename',guideid,t_guide.tel AS 'tel',address,peoplenum,money,price,time FROM t_list LEFT JOIN t_company ON t_list.compayid = t_company.id LEFT JOIN t_guide ON t_list.guideid = t_guide.id";
		return db.executeQueryForList(sql, new SalesRowMapper());
		
		 
	}
public class SalesRowMapper implements RowMapper<Sales>{
		
		public Sales mapperRow (ResultSet rs) throws SQLException{
			
			Sales sales = new Sales();
			sales.setAddress(rs.getString("address"));
			sales.setCompayid(rs.getString("compayid"));
			sales.setGuideid(rs.getString("guideid"));
			sales.setId(rs.getString("id"));
			sales.setMoney(rs.getFloat("money"));
			sales.setPeoplenum(rs.getInt("peoplenum"));
			sales.setPrice(rs.getFloat("price"));
			sales.setTime(rs.getString("time"));
			
			
			Company company = new Company();
			company.setId(rs.getString("compayid"));
			company.setName(rs.getString("name"));
			sales.setCompany(company);
			
			Guide guide = new Guide();
			guide.setId(rs.getString("guideid"));
			guide.setGuidename(rs.getString("guidename"));
			guide.setTel(rs.getString("tel"));
			sales.setGuide(guide);
			
			return sales;
			
		}
	}
public Sales findyId(String id) {
	String sql="SELECT t_list.id,compayid,t_company.name AS 'name',t_guide.guidename AS 'guidename',guideid,t_guide.tel AS 'tel',address,peoplenum,money,price,time FROM t_list LEFT JOIN t_company ON t_list.compayid = t_company.id LEFT JOIN t_guide ON t_list.guideid = t_guide.id WHERE t_list.id = ?";
	return db.executeQueryForObject(sql, new SalesRowMapper(), id);
}

public void update(Sales sales) {
	String sql = "update t_list set time = ?,compayid = ?,guideid = ?, peoplenum = ?, address = ?, money = ?, price = ?   where id = ?";
	db.executeSQL(sql, sales.getTime(),sales.getCompany().getId(),sales.getGuide().getId(),sales.getPeoplenum(),sales.getAddress(),sales.getMoney(),sales.getPrice(),sales.getId());
	
}

public void del(String id) {
	
	String sql ="delete from t_list where id = ?";
	db.executeSQL(sql, id);
	
}

public Pager<Sales> findByWhere(int pageNum, String companyname,
		String guidename, String address, String starttime, String endtime,
		String minmoney, String maxmoney) {
	
	String where = buildWhere(companyname,guidename,address,starttime,endtime,minmoney,maxmoney);
	
	List<Sales> list = new ArrayList<Sales>();
	
	int count = count(where);
	int peoplenum = sumpeople(where);
	float money = sumMoney(where);
	
	Pager<Sales> pager = new Pager<Sales>(10,count,pageNum);
	if(count > 0) {
		StringBuilder sql = new StringBuilder();
		sql.append("select t_list.id,compayid,t_company.`name` as 'name',t_guide.`guidename` as 'guidename',guideid,t_guide.tel as 'tel',address,peoplenum,money,price,time ");
		sql.append("from t_list ");
		sql.append("left join t_company on t_company.id = t_list.`compayid` ");
		sql.append("left join `t_guide` on t_guide.id = t_list.`guideid` ");
		sql.append(where);
		sql.append("order by time desc ");
		sql.append("limit ?,?");
		list = db.executeQueryForList(sql.toString(), new SalesRowMapper(),pager.getStartNum(),pager.getPageSize());
	}
	pager.setResult(list);
	pager.setTotalMoney(money);
	pager.setTotalpeoplenum(peoplenum);
	pager.setTotalprice(pager.getTotalMoney()/pager.getTotalpeoplenum());
	
	return pager;
	
	
}
private float sumMoney(String where) {
StringBuilder countSql = new StringBuilder("select sum(money) from t_list ");
	
	countSql.append("left join t_company on t_company.id = t_list.`compayid` ");
	countSql.append("left join t_guide on t_guide.id = t_list.`guideid` ");
	countSql.append(where);
	
	float sumMoney = db.executeQueryForCount(countSql.toString());
	return sumMoney;
}

private int sumpeople(String where){
	
	StringBuilder countSql = new StringBuilder("select sum(peoplenum) from t_list ");
	
	countSql.append("left join t_company on t_company.id = t_list.`compayid` ");
	countSql.append("left join t_guide on t_guide.id = t_list.`guideid` ");
	countSql.append(where);
	
	int sumpeople = db.executeQueryForCount(countSql.toString());
	return sumpeople;
	
}

private int count(String where) {
	
			StringBuilder countSql = new StringBuilder("select count(*) from t_list ");
			
			countSql.append("left join t_company on t_company.id = t_list.`compayid` ");
			countSql.append("left join t_guide on t_guide.id = t_list.`guideid` ");
			countSql.append(where);
			
			int count = db.executeQueryForCount(countSql.toString());
			return count;
}

private String buildWhere(String companyname, String guidename, String address,
		String starttime, String endtime, String minmoney, String maxmoney) {
	
	StringBuilder where = new StringBuilder();
	
	where.append("where ");
	if(StringUtil.isNotEmpty(companyname)) {
		where.append("t_company.name = '").append(companyname).append("' and ");
	}
	if(StringUtil.isNotEmpty(guidename)) {
		where.append("t_guide.guidename = '").append(guidename).append("' and ");
	}
	if(StringUtil.isNotEmpty(address)) {
		where.append("address = '").append(address).append("' and ");
	}
	if(StringUtil.isNotEmpty(starttime)) {
		where.append("time >= '" ).append(starttime).append("' and ");
	}
	if(StringUtil.isNotEmpty(endtime)) {
		where.append("time <= '" ).append(endtime).append("' and ");
	}
	if(StringUtil.isNotEmpty(minmoney)) {
		where.append("money >= " ).append(minmoney).append (" and ");
	}
	if(StringUtil.isNotEmpty(maxmoney)) {
		where.append("money <= " ).append(maxmoney).append(" ");
	}
	
	String whereSql = where.toString();
	if(whereSql.endsWith("where ")) {
		whereSql = "";
	}
	if(whereSql.endsWith("and ")) {
		whereSql = whereSql.substring(0,whereSql.lastIndexOf("and "));
	}
	
	return whereSql;

}

public List<Sales> findByWheres(String companyname, String guidename,
		String address, String starttime, String endtime, String minmoney,
		String maxmoney) {
		
	String where = buildWhere(companyname,guidename,address,starttime,endtime,minmoney,maxmoney);
	
	StringBuilder sql = new StringBuilder();
	
	sql.append("select t_list.id,compayid,t_company.`name` as 'name',t_guide.`guidename` as 'guidename',guideid,t_guide.tel as 'tel',address,peoplenum,money,price,time ");
	sql.append("from t_list ");
	sql.append("left join t_company on t_company.id = t_list.`compayid` ");
	sql.append("left join `t_guide` on t_guide.id = t_list.`guideid` ");
	sql.append(where);
	sql.append("order by time desc ");
	
	return db.executeQueryForList(sql.toString(), new SalesRowMapper());
	
}

}
